﻿Imports System.Data.SqlClient
Imports System.Configuration
Imports SIGI.Common.BE

Public Class ClientDAC

#Region "<< -SearchByParams- >>"
    Public Shared Function SearchByParams(pClientParams As ClientBE) As List(Of ClientBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty
        Dim wClientList As List(Of ClientBE) = Nothing
        Dim wClient As ClientBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "[dbo].[Clientes_g]"


            wSqlCmd.Parameters.Add("@Id_Cliente", SqlDbType.Int).Value = pClientParams.Id
            If Not String.IsNullOrEmpty(pClientParams.Name) Then
                wSqlCmd.Parameters.Add("@Nombre", SqlDbType.NVarChar, 150).Value = pClientParams.Name
            End If
            If Not pClientParams.Sex Is Nothing Then
                wSqlCmd.Parameters.Add("@Sexo", SqlDbType.Int).Value = pClientParams.Sex.Id
            End If
            If Not pClientParams.DocumentType Is Nothing Then
                wSqlCmd.Parameters.Add("@Tipo_Doc", SqlDbType.Int).Value = pClientParams.DocumentType.Id
            End If
            wSqlCmd.Parameters.Add("@DNI", SqlDbType.Decimal).Value = pClientParams.DocumentNumber
            If Not String.IsNullOrEmpty(pClientParams.Cuit) Then
                wSqlCmd.Parameters.Add("@CUIT", SqlDbType.NVarChar, 15).Value = pClientParams.Cuit
            End If
            If Not String.IsNullOrEmpty(pClientParams.PostalCode) Then
                wSqlCmd.Parameters.Add("@Codigo_Postal", SqlDbType.NVarChar, 15).Value = pClientParams.PostalCode
            End If
            If Not String.IsNullOrEmpty(pClientParams.Direction) Then
                wSqlCmd.Parameters.Add("@Direccion", SqlDbType.NVarChar, 50).Value = pClientParams.Direction
            End If
            If Not pClientParams.City Is Nothing Then
                wSqlCmd.Parameters.Add("@Id_Ciudad", SqlDbType.Int).Value = pClientParams.City.Id
            End If
            If Not pClientParams.Province Is Nothing Then
                wSqlCmd.Parameters.Add("@Id_Provincia", SqlDbType.Int).Value = pClientParams.Province.Id
            End If
            If Not pClientParams.Country Is Nothing Then
                wSqlCmd.Parameters.Add("@Id_Pais", SqlDbType.Int).Value = pClientParams.Country.Id
            End If
            If Not String.IsNullOrEmpty(pClientParams.Phone) Then
                wSqlCmd.Parameters.Add("@Telefono", SqlDbType.NChar, 30).Value = pClientParams.Phone
            End If
            If Not String.IsNullOrEmpty(pClientParams.AuxiliarPhone) Then
                wSqlCmd.Parameters.Add("@TelefonoAuxiliar", SqlDbType.NChar, 30).Value = pClientParams.AuxiliarPhone
            End If
            wSqlCmd.Parameters.Add("@Activo", SqlDbType.Bit).Value = Not pClientParams.EndDate.HasValue

            wSqlConn.Open()

            wReader = wSqlCmd.ExecuteReader()
            wClientList = New List(Of ClientBE)
            While wReader.Read()
                wClient = New ClientBE

                wClient.Id = Convert.ToInt32(wReader("Id_Cliente"))
                wClient.Name = Convert.ToString(wReader("Nombre"))
                wClient.Sex = New SexBE With {
                                              .Id = Convert.ToInt32(wReader("Id_Sexo")),
                                              .Description = Convert.ToString("Sexo")
                                              }
                wClient.DocumentNumber = Convert.ToInt32(wReader("DNI"))
                wClient.DocumentType = New DocumentTypeBE With {
                                                                .Id = Convert.ToInt32(wReader("Id_TipoDoc")),
                                                                .Description = Convert.ToString(wReader("TipoDocDescripcion"))
                                                                }
                If Not wReader("CUIT") Is DBNull.Value Then
                    wClient.Cuit = Convert.ToString(wReader("CUIT"))
                End If
                wClient.PostalCode = Convert.ToString(wReader("Codigo_Postal"))
                wClient.Direction = Convert.ToString(wReader("Direccion"))
                wClient.City = New CityBE With {
                                                .Id = Convert.ToInt32(wReader("Id_Ciudad")),
                                                .Description = Convert.ToString(wReader("Ciudad"))
                                                }
                wClient.Province = New ProvinceBE With {
                                                        .Id = Convert.ToInt32(wReader("Id_Provincia")),
                                                        .Description = Convert.ToString(wReader("Provincia"))
                                                       }
                wClient.Country = New CountryBE With {
                                                      .Id = Convert.ToInt32(wReader("Id_Pais")),
                                                      .Description = Convert.ToString("Pais")
                                                      }
                wClient.Phone = Convert.ToString(wReader("Telefono"))
                If Not wReader("TelefonoAuxiliar") Is DBNull.Value Then
                    wClient.AuxiliarPhone = Convert.ToString(wReader("TelefonoAuxiliar"))
                End If
                If Not wReader("Fecha_Baja") Is DBNull.Value Then
                    wClient.EndDate = Convert.ToDateTime(wReader("Fecha_Baja"))
                End If
                If Not wReader("Motivo_Baja") Is DBNull.Value Then
                    wClient.AccountCloseMotive = Convert.ToString(wReader("Motivo_Baja"))
                End If
                If Not wReader("Email") Is DBNull.Value Then
                    wClient.Email = Convert.ToString(wReader("Email"))
                End If

                wClientList.Add(wClient)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wClientList
    End Function
#End Region

#Region "<< -Search- >>"
    Public Shared Function Search(pActive As Boolean) As List(Of ClientBE)
        Dim wClient As ClientBE = New ClientBE
        wClient.EndDate = IIf(pActive, Nothing, Date.MinValue)
        Return SearchByParams(wClient)
    End Function
#End Region

#Region "<< -Insert- >>"
    Public Shared Function Insert(pClient As ClientBE) As Integer
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty
        Dim wId As Integer = 0

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "[dbo].[Clientes_i]"

            wSqlCmd.Parameters.Add("@Nombre", SqlDbType.NVarChar, 150).Value = pClient.Name
            wSqlCmd.Parameters.Add("@Sexo", SqlDbType.Int).Value = pClient.Sex.Id
            wSqlCmd.Parameters.Add("@Tipo_Doc", SqlDbType.Int).Value = pClient.DocumentType.Id
            wSqlCmd.Parameters.Add("@DNI", SqlDbType.Decimal).Value = pClient.DocumentNumber
            If Not String.IsNullOrEmpty(pClient.Cuit) Then
                wSqlCmd.Parameters.Add("@CUIT", SqlDbType.NVarChar, 15).Value = pClient.Cuit
            End If
            wSqlCmd.Parameters.Add("@Codigo_Postal", SqlDbType.NVarChar, 15).Value = pClient.PostalCode
            wSqlCmd.Parameters.Add("@Direccion", SqlDbType.NVarChar, 50).Value = pClient.Direction
            wSqlCmd.Parameters.Add("@Id_Ciudad", SqlDbType.Int).Value = pClient.City.Id
            wSqlCmd.Parameters.Add("@Id_Provincia", SqlDbType.Int).Value = pClient.Province.Id
            wSqlCmd.Parameters.Add("@Id_Pais", SqlDbType.Int).Value = pClient.Country.Id
            wSqlCmd.Parameters.Add("@Telefono", SqlDbType.NChar, 30).Value = pClient.Phone
            If Not String.IsNullOrEmpty(pClient.AuxiliarPhone) Then
                wSqlCmd.Parameters.Add("@TelefonoAuxiliar", SqlDbType.NChar, 30).Value = pClient.AuxiliarPhone
            End If
            If Not String.IsNullOrEmpty(pClient.Email) Then
                wSqlCmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100).Value = pClient.Email
            End If

            wSqlConn.Open()
            wReader = wSqlCmd.ExecuteReader()
            wReader.Read()
            wId = Convert.ToInt32(wReader("Id"))
            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wId
    End Function
#End Region

#Region "<< -Update- >>"
    Public Shared Sub Update(pClient As ClientBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "[dbo].[Clientes_u]"

            wSqlCmd.Parameters.Add("@Id_Cliente", SqlDbType.Int).Value = pClient.Id
            wSqlCmd.Parameters.Add("@Nombre", SqlDbType.NVarChar, 150).Value = pClient.Name
            wSqlCmd.Parameters.Add("@Sexo", SqlDbType.Int).Value = pClient.Sex.Id
            wSqlCmd.Parameters.Add("@Tipo_Doc", SqlDbType.Int).Value = pClient.DocumentType.Id
            wSqlCmd.Parameters.Add("@DNI", SqlDbType.Decimal).Value = pClient.DocumentNumber
            If Not String.IsNullOrEmpty(pClient.Cuit) Then
                wSqlCmd.Parameters.Add("@CUIT", SqlDbType.NVarChar, 15).Value = pClient.Cuit
            End If
            wSqlCmd.Parameters.Add("@Codigo_Postal", SqlDbType.NVarChar, 15).Value = pClient.PostalCode
            wSqlCmd.Parameters.Add("@Direccion", SqlDbType.NVarChar, 50).Value = pClient.Direction
            wSqlCmd.Parameters.Add("@Id_Ciudad", SqlDbType.Int).Value = pClient.City.Id
            wSqlCmd.Parameters.Add("@Id_Provincia", SqlDbType.Int).Value = pClient.Province.Id
            wSqlCmd.Parameters.Add("@Id_Pais", SqlDbType.Int).Value = pClient.Country.Id
            wSqlCmd.Parameters.Add("@Telefono", SqlDbType.NChar, 30).Value = pClient.Phone
            If Not String.IsNullOrEmpty(pClient.AuxiliarPhone) Then
                wSqlCmd.Parameters.Add("@TelefonoAuxiliar", SqlDbType.NChar, 30).Value = pClient.AuxiliarPhone
            End If
            If Not String.IsNullOrEmpty(pClient.Email) Then
                wSqlCmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100).Value = pClient.Email
            End If
            wSqlCmd.Parameters.Add("@Fecha_Baja", SqlDbType.DateTime).Value = pClient.EndDate
            If Not String.IsNullOrEmpty(pClient.AccountCloseMotive) Then
                wSqlCmd.Parameters.Add("@Motivo_Baja", SqlDbType.NVarChar, 500).Value = pClient.AccountCloseMotive
            End If

            wSqlConn.Open()
            wSqlCmd.ExecuteNonQuery()
            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region

#Region "<< -Delete- >>"
    Public Shared Function Delete(pClient As ClientBE) As Boolean
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty
        Dim wDeleted As Boolean = False

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "[dbo].[Clientes_d]"

            wSqlCmd.Parameters.Add("@Id_Cliente", SqlDbType.Int).Value = pClient.Id
            wSqlCmd.Parameters.Add("@Fecha_Baja", SqlDbType.DateTime).Value = pClient.EndDate
            If Not String.IsNullOrEmpty(pClient.AccountCloseMotive) Then
                wSqlCmd.Parameters.Add("@Motivo_Baja", SqlDbType.NVarChar, 500).Value = pClient.AccountCloseMotive
            End If

            wSqlConn.Open()

            wReader = wSqlCmd.ExecuteReader()
            wReader.Read()
            wDeleted = Convert.ToBoolean(wReader(0))
            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wDeleted

    End Function
#End Region

End Class
